﻿using EasyERP_Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EasyERP_DAL.Wares
{
    /// <summary>
    /// 往来单价数据访问类
    /// </summary>
    public class B_Item_Price_DAL
    {
        #region 查询商品往来单价信息
        /// <summary>
        /// 查询商品往来单价信息
        /// </summary>
        /// <param name="item_Price">往来单价实体类</param>
        /// <param name="item">商品信息实体类</param>
        /// <returns>返回商品往来单价信息</returns>
        public DataTable Get_B_Item_Price(B_Item_Price item_Price, B_Item item)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT a.bp_cd,
                       b.bp_nm,
                       a.item_cd,
                       c.item_nm,
                       c.item_spec,
                       a.item_price,
                       dbo.Get_Minor_nm(N'1003', c.item_unit) AS item_unit_nm,
                       dbo.Get_Minor_nm(N'1004', c.item_group) AS item_group_nm,
                       a.remark
                FROM dbo.b_item_price AS a
                    INNER JOIN dbo.b_partner AS b
                        ON b.bp_cd = a.bp_cd
                    INNER JOIN dbo.b_item AS c
                        ON c.item_cd = a.item_cd
                WHERE a.bp_cd = N'{item_Price.bp_cd}'
                      AND CASE
                              WHEN N'{item.item_group}' = '' THEN
                                  ''
                              ELSE
                                  c.item_group
                          END = N'{item.item_group}'
                      AND c.item_nm LIKE '%{item.item_nm}%';
            ");

            return DbHelperSQL.Query(sb.ToString()).Tables[0];
        }
        #endregion

        #region 添加商品单价信息
        /// <summary>
        /// 添加商品单价信息
        /// </summary>
        /// <param name="model">商品单价实体类</param>
        /// <returns>返回True：成功；False：失败；</returns>
        public bool Insert_B_Item_Price(B_Item_Price model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                INSERT INTO dbo.b_item_price
                (
                    bp_cd,
                    item_cd,
                    item_price,
                    remark,
                    ins_user,
                    ins_date,
                    up_user,
                    up_date
                )
                VALUES
                (   N'{model.bp_cd}',           -- bp_cd - nvarchar(10)
                    N'{model.item_cd}',         -- item_cd - nvarchar(10)
                    {model.item_price},         -- item_price - numeric(18, 4)
                    N{model.remark},            -- remark - nvarchar(200)
                    N'{model.user_cd}',         -- ins_user - nvarchar(6)
                    GETDATE(),                  -- ins_date - datetime
                    N'{model.user_cd}',         -- up_user - nvarchar(6)
                    GETDATE()                   -- up_date - datetime
                    );
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 删除商品单价信息
        /// <summary>
        /// 删除商品单价信息
        /// </summary>
        /// <param name="model">商品单价实体类</param>
        /// <returns>返回True：成功；False：失败；</returns>
        public bool Delete_B_Item_Price(B_Item_Price model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                DELETE FROM dbo.b_item_price
                WHERE bp_cd = N'{model.bp_cd}'
                      AND item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 修改商品单价信息
        /// <summary>
        /// 修改商品单价信息
        /// </summary>
        /// <param name="model">商品单价实体类</param>
        /// <returns>返回True：成功；False：失败；</returns>
        public bool Update_B_Item_Price(B_Item_Price model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                UPDATE dbo.b_item_price
                SET item_price = '{model.item_price}',
                    remark = N'{model.remark}',
                    up_user = N'{model.user_cd}',
                    up_date = GETDATE()
                WHERE bp_cd = N'{model.bp_cd}'
                      AND item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 查询数据是否存在
        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="model">商品信息实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists(B_Item_Price model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.b_item_price
                WHERE bp_cd = N'{model.bp_cd}'
                      AND item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        }
        #endregion
    }
}
